<?php 
	class Subjects extends CI_Model{

		function __construct(){
			parent::__construct();
		}

		function get_subject_list($limit = 0, $offset = 0, $conditions = array()){
			$where = "tp.id > 0";
			if($conditions){
				if(!empty($conditions['title'])){
					$where .= " AND s.title LIKE '%{$conditions['title']}%'";
				}

				if(!empty($conditions['tk_category_id'])){
					$where .= " AND s.tk_category_id = '{$conditions['tk_category_id']}'";
				}

				if(!empty($conditions['school_id'])){
					$where .= " AND (c.school_id='{$conditions['school_id']}' OR c.school_id = 0) AND (s.school_id = '{$conditions['school_id']}' OR s.school_id = 0)";
				}
			}
			
			$sql = "SELECT SQL_CALC_FOUND_ROWS s.*, tp.id AS pack_id, c.name FROM {$this->db->dbprefix}b_subject AS s ";
			$sql .= "LEFT JOIN {$this->db->dbprefix}tk_categorys AS c ON(c.id=s.tk_category_id) ";
			$sql .= "LEFT JOIN {$this->db->dbprefix}o2o_school_tk_packages AS tp ON(tp.school_id = '{$conditions['school_id']}' AND tp.subject_id=s.id) ";
			$sql .= "WHERE $where ORDER BY s.id DESC ";
			if($limit > 0 || $offset > 0){
			   $sql .= "LIMIT $limit, $offset";
			}
			$result = $this->db->query($sql)->result_array();
			$row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
			return array("count" => $row_num['row_num'], "result" => $result);
		}

		function check_subject_buystatus($course_id, $course_type = 4){
			$sql = "SELECT o.order_status FROM {$this->db->dbprefix}shopping_order_item AS item ";
			$sql .= "LEFT JOIN {$this->db->dbprefix}shopping_orders AS o ON(o.order_id=item.order_id AND o.buyer_user_id = '{$this->user_data['id']}')";
			$sql .= "WHERE item.course_type = '{$course_type}' AND item.course_id='{$course_id}' AND o.order_status = 1 LIMIT 1";
			$result = $this->db->query($sql)->row_array();
			return $result['order_status'];
		}

		function get_student_subject_list($limit = 0, $offset = 0, $conditions = array()){
			$where = "1=1";
			if($conditions){
				if(!empty($conditions['title'])){
					$where .= " AND s.title LIKE '%{$conditions['title']}%'";
				}

				if(!empty($conditions['tk_category_id'])){
					$where .= " AND s.tk_category_id = '{$conditions['tk_category_id']}'";
				}

				if(!empty($conditions['school_id'])){
					$where .= "  AND (s.school_id = '{$conditions['school_id']}' OR s.school_id = 0)";
				}

				if(!empty($conditions['uid'])){
					// $where .= " AND it.data_type=4 AND it.uid='{$conditions['uid']}' ";
				}
			}
			
			$sql = "SELECT SQL_CALC_FOUND_ROWS s.*, c.name, it.id AS iid FROM {$this->db->dbprefix}b_subject AS s ";
			$sql .= "LEFT JOIN {$this->db->dbprefix}tk_categorys AS c ON(c.id=s.tk_category_id) ";
			$sql .= "LEFT JOIN {$this->db->dbprefix}o2o_student_item_terms AS it ON(it.data_id = s.id AND it.data_type=4 AND it.uid='{$conditions['uid']}') ";
			$sql .= "WHERE $where ORDER BY s.id DESC ";
			if($limit > 0 || $offset > 0){
			   $sql .= "LIMIT $limit, $offset";
			}
			$result = $this->db->query($sql)->result_array();
			$row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
			return array("count" => $row_num['row_num'], "result" => $result);
		}

		function get_chapter_wrang_topic($limit = 0, $offset = 0, $conditions = array()){
			$where = "WHERE (th.id > 0 OR s.is_chapter=0)";
			if(isset($conditions['subject_id'])){
				$where .= " AND s.subject_id = '{$conditions['subject_id']}'";
			}

			$sql = "SELECT SQL_CALC_FOUND_ROWS s.*, ps.title as ptitle, COUNT(th.id) AS wrang_count FROM {$this->db->dbprefix}b_subject_chapter AS s ";
			$sql .= " LEFT JOIN {$this->db->dbprefix}tk_test_topic_historys AS th ON(th.chapter_id=s.id AND th.volume_id=0 AND volume_history_id=0 AND th.is_right=0 AND th.uid = '{$conditions['uid']}')";
			$sql .= "LEFT JOIN {$this->db->dbprefix}b_subject_chapter AS ps ON(ps.id=s.parent_id) ";
			$sql .= " $where GROUP BY s.id ORDER BY s.sort ASC LIMIT $limit, $offset";
			$result = $this->db->query($sql)->result_array();
			$row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
			return array("count" => $row_num['row_num'], "result" => $result);
		}

		function get_chapter_right_topic($limit = 0, $offset = 0, $conditions = array()){
			$where = "WHERE t.id NOT IN(SELECT topic_id FROM {$this->db->dbprefix}tk_test_topic_historys WHERE chapter_id = s.id AND volume_id = 0  AND volume_history_id = 0  AND uid = '{$conditions['uid']}')";
			if(isset($conditions['subject_id'])){
				$where .= " AND s.subject_id = '{$conditions['subject_id']}'";
			}
			$sql = "SELECT SQL_CALC_FOUND_ROWS s.*, ps.title as ptitle, COUNT(t.id) AS right_count FROM {$this->db->dbprefix}b_subject_chapter AS s ";
			$time = time();
			// $sql .= "  LEFT JOIN {$this->db->dbprefix}tk_topics AS t ON (t.chapter_id=s.`id` AND (t.school_id = '{$conditions['school_id']}' OR (t.category_id IN(SELECT category_id FROM {$this->db->dbprefix}o2o_school_system_topics WHERE school_id='{$conditions['school_id']}' AND add_time <= $time AND expire_tiem >= $time AND t.category_id IN (SELECT category_id FROM {$this->db->dbprefix}o2o_student_system_topics WHERE uid='{$conditions['uid']}' AND schoo_id='{$conditions['school_id']}' AND expire_tiem >= $time))))) ";
			$sql .= "  LEFT JOIN {$this->db->dbprefix}tk_topics AS t ON (t.chapter_id=s.`id` AND (t.school_id = '{$conditions['school_id']}' OR school_id = 0)) ";
			$sql .= "LEFT JOIN {$this->db->dbprefix}b_subject_chapter AS ps ON(ps.id=s.parent_id) ";
			$sql .= " $where GROUP BY s.id HAVING right_count > 0 ORDER BY s.sort ASC LIMIT $limit, $offset";
			$result = $this->db->query($sql)->result_array();
			$row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
			return array("count" => $row_num['row_num'], "result" => $result);
		}

		function get_chapter_list_topic($limit = 0, $offset = 0, $conditions = array()){
			$where = "WHERE 1=1";
			if(isset($conditions['subject_id'])){
				$where .= " AND s.subject_id = '{$conditions['subject_id']}'";
			}
			$sql = "SELECT SQL_CALC_FOUND_ROWS s.*, ps.title as ptitle, COUNT(t.id) AS count, count(th.id) AS historys_count, count(thr.id) AS historys_right_count FROM {$this->db->dbprefix}b_subject_chapter AS s ";
			$time = time();
			// $sql .= "  LEFT JOIN {$this->db->dbprefix}tk_topics AS t ON (t.chapter_id=s.`id` AND (t.school_id = '{$conditions['school_id']}' OR (t.category_id IN(SELECT category_id FROM {$this->db->dbprefix}o2o_school_system_topics WHERE school_id='{$conditions['school_id']}' AND add_time <= $time AND expire_tiem >= $time AND t.category_id IN (SELECT category_id FROM {$this->db->dbprefix}o2o_student_system_topics WHERE uid='{$conditions['uid']}' AND schoo_id='{$conditions['school_id']}' AND expire_tiem >= $time))))) ";
			$sql .= "  LEFT JOIN {$this->db->dbprefix}tk_topics AS t ON (t.chapter_id=s.`id` AND (t.school_id = '{$conditions['school_id']}' OR school_id = 0)) ";
			$sql .= " LEFT JOIN {$this->db->dbprefix}tk_test_topic_historys AS th ON(th.topic_id=t.id AND th.uid='{$this->user_data['id']}') ";
			$sql .= " LEFT JOIN {$this->db->dbprefix}tk_test_topic_historys AS thr ON(thr.topic_id=t.id AND thr.uid='{$this->user_data['id']}') AND thr.is_right = 1 ";
			$sql .= "LEFT JOIN {$this->db->dbprefix}b_subject_chapter AS ps ON(ps.id=s.parent_id) ";
			$sql .= " $where GROUP BY s.id HAVING count > 0 ORDER BY s.sort ASC LIMIT $limit, $offset";
			$result = $this->db->query($sql)->result_array();
			$row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
			return array("count" => $row_num['row_num'], "result" => $result);
		}

		function get_subject_info($subject_id, $uid, $school_id){
			$time = time();
			$sql = "SELECT s.*, st.expire_tiem FROM {$this->db->dbprefix}b_subject AS s ";
			$sql .= "LEFT JOIN {$this->db->dbprefix}o2o_student_system_topics AS st ON(st.uid='{$uid}' AND st.subject_id=s.id) ";
			$sql .= "WHERE s.id=$subject_id AND s.school_id = $school_id OR (s.school_id=0 AND st.add_time <= $time AND st.expire_tiem >= $time)";
			$result = $this->db->query($sql)->row_array();
			return $result;
		}

		function get_school_subject_list($conditions = array()){
			$where = 'status = 1 AND tp.id > 0 AND s.id > 0';
			if(!empty($conditions['school_id'])){
				$where .= " AND tp.school_id = '{$conditions['school_id']}'";
			}
			if(!empty($conditions['tk_category_id'])){
				$where .= " AND tp.tk_category_id = '{$conditions['tk_category_id']}'";
			}
			$sql = "SELECT SQL_CALC_FOUND_ROWS tp.*, s.title FROM {$this->db->dbprefix}o2o_school_tk_packages AS tp ";
			$sql .= "LEFT JOIN {$this->db->dbprefix}b_subject AS s ON(s.id=tp.subject_id) ";
			$sql .= " WHERE $where";
			$result = $this->db->query($sql)->result_array();
			$row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
			return array("count" => $row_num['row_num'], "result" => $result);
		}
	}